//
//  MadDatabase.c
//  MadGTD
//
//  Created by Fuzhou Chen on 4/30/12.
//  Copyright (c) 2012 Fuzhou Chen. All rights reserved.
//

#include <assert.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#include <sys/time.h>
#include "MadDatabase.h"
#include "MadDebug.h"

#define MAD_ALLOC     malloc
#define MAD_FREE      free

// yyyy = 2 bytes
// mm   = 1 byte
// dd   = 1 bytes
// hh   = 1 byte
// MM   = 1 byte
// ss   = 1 byte
// milliseconds = 2 bytes
#define MAD_DT_LENGTH 9

// ASCII code of "TODO"
static const char* MAD_TODO_MAGIC_NUMBER_STR = "1TO746f646f";
// ASCII code of "TAG"
static const char* MAD_TAG_MAGIC_NUMBER_STR  = "1TA00746167";

static const char* MAD_MAGIC_TABLE_NAME        = "madMagic";
static const char* MAD_TODO_TABLE_NAME         = "madTodo";
static const char* MAD_TAG_TABLE_NAME          = "madTag";
static const char* MAD_TODO_TAG_MAP_TABLE_NAME = "madTodoTagMapping";
static const char* MAD_MAGIC_CHECK_QUERY       = "select * from madMagic;";

static const char* MAD_MAGIC_CREATE_QUERY = "create table madMagic "
                                            "(madTodo text, madTag text);";

static const char* MAD_TODO_CREATE_QUERY  = "create table madTodo "
                                            "(objId blob, content text, "
                                            " due blob, complete blob,"
                                            " lastUpdate blob, done integer);";

static const char* MAD_TAG_CREATE_QUERY   = "create table madTag "
                                            "(objId blob, name blob,"
                                            " baseMark integer, lastUpdate blob);";
static const char* MAD_MAPPING_CREATE_QUERY = "create table madTodoTagMapping "
                                              "(todoId text, tagId text);";
static const char* MAD_MAGIC_INSERT_QUERY = "insert into madMagic "
                                         "(madTodo, madTag) values "
                                         "('1TO746f646f', '1TA00746167');";
static const char* MAD_BEGIN = "begin;";
static const char* MAD_COMMIT = "commit;";

struct _MadDTBlob
{
    uint8_t blob[MAD_DT_LENGTH];
};

struct _MadDatabase
{
    sqlite3* db;
};

void
MadConvertUTC(MadDTBlob* dtBlob, time_t dtsec, suseconds_t msecs)
{
    struct tm* rettime = NULL;
    rettime = gmtime(&dtsec);
    dtBlob->blob[0] = ((rettime->tm_year & 0xff00) >> 8);
    dtBlob->blob[1] = (rettime->tm_year & 0xff);
    dtBlob->blob[2] = rettime->tm_mon;
    dtBlob->blob[3] = rettime->tm_mday;
    dtBlob->blob[4] = rettime->tm_hour;
    dtBlob->blob[5] = rettime->tm_min;
    dtBlob->blob[6] = rettime->tm_min;
    dtBlob->blob[7] = ((msecs & 0xff00) >> 8);
    dtBlob->blob[8] = (msecs & 0xff);
    return;
}
void
MadGetCurrentUTC(MadDTBlob* dtBlob)
{
    struct timeval currentdt;
    struct timezone tz;
    gettimeofday(&currentdt, &tz);
    MadConvertUTC(dtBlob, currentdt.tv_sec, currentdt.tv_usec);
    return;
}

//// Private utilty functions
static int
MagicCheckCB(void* context, int columes,
             char** columeValues, char** columeNames)
{
    int i = 0;
    int isTodosMagicMatched = 0;
    int isTagsMagicMatched = 0;
    for (; i < columes; ++i)
    {
        if (strcmp(columeNames[i], MAD_TODO_TABLE_NAME) == 0 &&
                strcmp(columeValues[i], MAD_TODO_MAGIC_NUMBER_STR) == 0)
        {
            isTodosMagicMatched = 1;
        }
        else if (strcmp(columeNames[i], MAD_TAG_TABLE_NAME) == 0 &&
                strcmp(columeValues[i], MAD_TAG_MAGIC_NUMBER_STR) == 0)
        {
            isTagsMagicMatched = 1;
        }
    }
    (*((int*)context)) = (isTodosMagicMatched && isTagsMagicMatched);
    return SQLITE_OK;
}
static int
DbIsInitialized(sqlite3* db)
{
    int sqlResult = 0;
    int isInit = 0;
    sqlResult = sqlite3_exec(db, MAD_MAGIC_CHECK_QUERY,
                             MagicCheckCB, &isInit, NULL);
    // No need to check return value. If check fails, it means the table
    // is not initialized.
Exit:
    return isInit;
}
static MadResult
DbInitialTables(sqlite3* db)
{
    MadResult result = MAD_SUCCESS;
    int sqlResult = 0;

    sqlResult = sqlite3_exec(db, MAD_BEGIN, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to begin transaction, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_MAGIC_CREATE_QUERY, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to create magic table, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_TODO_CREATE_QUERY, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to create todos table, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_TAG_CREATE_QUERY, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to create tags table, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_MAPPING_CREATE_QUERY,
                             NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to create mapping table, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_MAGIC_INSERT_QUERY,
                             NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to insert magic codes, result = %d", sqlResult));

    sqlResult = sqlite3_exec(db, MAD_COMMIT, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Failed to end transaction, result = %d", sqlResult));


    (void)MAD_MAGIC_TABLE_NAME;
    (void)MAD_TODO_TAG_MAP_TABLE_NAME;
Exit:
    return result;
}

// Public interfaces
MadResult
MadDbOpen(const char* dbFileName, MadDatabase** outNewDb)
{
    MadResult result = MAD_SUCCESS;
    MadDatabase *newDb = NULL;
    int sqlResult = 0;

    MAD_CHK_PARAM(dbFileName != NULL, ("dbFileName == NULL"));
    MAD_CHK_PARAM(outNewDb != NULL, ("outNewDb == NULL"));

    // We make sure timezone is correct when we start accessing
    // database.
    tzset();

    newDb = MAD_ALLOC(sizeof(*newDb));
    MAD_CHK(newDb != NULL, MAD_ERR_NO_MEMORY, ("Alloc database object"));
    sqlResult = sqlite3_open(dbFileName, &(newDb->db));
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
            ("Can't open database, code = %d", sqlResult));

    if (!DbIsInitialized(newDb->db))
    {
        sqlResult = DbInitialTables(newDb->db);
        MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_OPEN,
                ("Failed to initialize database, result = %d", sqlResult));
    }

    (*outNewDb) = newDb;
    newDb = NULL;
Exit:
    if (newDb != NULL)
        MadDbClose(newDb);
    return result;
}

void
MadDbClose(MadDatabase* db)
{
    if (db != NULL)
    {
        assert(db->db != NULL);
        sqlite3_close(db->db);
        MAD_FREE(db);
    }
}

MadResult
MadDbAddTodo(MadDatabase* db, MadTodo* todo)
{
    MadResult result = MAD_SUCCESS;
    int sqlResult = 0;
    sqlite3_stmt* stmt = NULL;
    MadUniqueId idBlob;
    MadDTBlob dtBlob;
    static char insert_todo_stmt[] =
        "insert into madTodo "
        "(objId, content, due, complete, lastUpdate, done) "
        "values (?1, ?2, ?3, ?4, ?5, ?6)";
    bool fBegin = false;
    int len = 0;
    int done = 0;

    MAD_CHK_PARAM(db != NULL, ("db == NULL"));
    MAD_CHK_PARAM(todo != NULL, ("todo == NULL"));

    sqlResult = sqlite3_exec(db->db, MAD_BEGIN, NULL, NULL, NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_RESOURCE_NOT_FOUND,
            ("sqlite3_exec(BEGIN, insert todo), result = %d", sqlResult));
    fBegin = true;

    sqlResult = sqlite3_prepare_v2(db->db,
                                   insert_todo_stmt,
                                   strlen(insert_todo_stmt),
                                   &stmt,
                                   NULL);
    MAD_CHK(sqlResult == SQLITE_OK, MAD_ERR_RESOURCE_NOT_FOUND,
            ("sqlite3_prepare_v2(insert todo), result = %d", sqlResult));

    // For new todo item, we simply ignore the object ID. The ID should
    // always be handled ourselves.
    MadGetCurrentUTC(&idBlob);
    sqlite3_bind_blob(stmt, 1, &idBlob, sizeof(MadUniqueId), SQLITE_STATIC);
    len = strlen(todo->content);
    sqlite3_bind_text(stmt, 2, todo->content, len, SQLITE_STATIC);
    MadConvertUTC(&dtBlob, todo->dueDateTime, 0);
    sqlite3_bind_blob(stmt, 3, &dtBlob, sizeof(MadDTBlob), SQLITE_STATIC);
    // When completeDateTime is set to 0, it means this item is still
    // active. However, as blob is always difficult to be compared, we
    // don't use it in database.
    done = (todo->completeDateTime == 0)? 0: 1;
    MadConvertUTC(&dtBlob, todo->completeDateTime, 0);
    sqlite3_bind_blob(stmt, 4, &dtBlob, sizeof(MadDTBlob), SQLITE_STATIC);
    // Also, the last update time should also be ignored as it should be
    // handled internally.
    MadGetCurrentUTC(&dtBlob);
    sqlite3_bind_blob(stmt, 5, &dtBlob, sizeof(MadDTBlob), SQLITE_STATIC);
    sqlite3_bind_int(stmt, 6, done);
    sqlResult = sqlite3_step(stmt);
    MAD_CHK(sqlResult == SQLITE_DONE, MAD_ERR_RESOURCE_NOT_FOUND,
            ("sqlite3_step(insert todo), result = %d", sqlResult));

    sqlite3_reset(stmt);

Exit:
    if (fBegin)
    {
        sqlResult = sqlite3_exec(db->db, MAD_COMMIT, NULL, NULL, NULL);
        if (sqlResult)
            MadCNSLog("sqlite3_exec(END, insert todo), result = %d",
                    sqlResult);
    }
    if (stmt != NULL)
        sqlite3_finalize(stmt);
    return result;
}

MadResult MadDbAddTag(MadDatabase* db, MadTag* tag);

MadResult
MadDbQueryTodos(MadDatabase* db, MadDbQueryFlag flag, MadTodo** todos)
{
    MadResult result = MAD_SUCCESS;
    int sqlResult = 0;
    static char query_active_stmt[] = "select * from madTodo where done = 1";
    static char query_done_stmt[] = "select * from madTodo where done = 0";
}

MadResult MadDbQueryTags(MadDatabase* db, MadTag** tags);
MadResult MadDbQueryTodosByTag(MadDatabase* db, MadTag* tag, MadTodo** outTodos);
MadResult MadDbQueryTagsByTodo(MadDatabase* db, MadTodo* todo, MadTag** outTags);
MadResult MadDbRemoveObjectInDb(MadDatabase* db, MadUniqueId* objId);
MadResult MadDbGetQueryTodosCount(MadTodo* todos);
MadResult MadDbGetQueryTagsCount(MadTag* tags);
MadResult MadDbAssignTag(MadDatabase* db, MadTodo* todo, MadTag* tag);
MadResult MadDbUnassignTag(MadDatabase* db, MadTodo* todo, MadTag* tag);


void MadDbTodoQueryDispose(MadTodo* info);
void MadDbTagQueryDispose(MadTag* info);
